import sqlite3

try:
    with sqlite3.connect("book.db") as conn:
        cursor = conn.cursor()

        create_table_sql = """
        CREATE TABLE IF NOT EXISTS sys_user (
            id INTEGER PRIMARY KEY,
            name VARCHAR(25) NOT NULL,
            age INTEGER
        );
        """
        cursor.execute(create_table_sql)
        print("表创建/检查完成")

        cursor.execute("DELETE FROM sys_user")

        insert_sql = "INSERT INTO sys_user(id, name, age) VALUES (?, ?, ?)"
        cursor.executemany(insert_sql, [(1, "root1", 18), (2, "root2", 18), (3, "root", 18), (4, "root", 18)])
        conn.commit()
        print("\n插入数据后：")

        select_sql = "SELECT * FROM sys_user"
        cursor.execute(select_sql)
        all_data = cursor.fetchall()
        for row in all_data:
            print(row)

        cursor.execute(select_sql)
        partial_data = cursor.fetchmany(3)
        print("\n获取前3条数据：")
        print(partial_data)

        update_sql = "UPDATE sys_user SET age = ? WHERE id = ?"
        cursor.execute(update_sql, (20, 1))
        conn.commit()
        print("\n更新id=1的数据后：")
        cursor.execute(select_sql)
        for row in cursor.fetchall():
            print(row)

        delete_sql = "DELETE FROM sys_user WHERE id = ?"
        cursor.execute(delete_sql, (4,))
        conn.commit()
        print("\n删除id=4的数据后：")
        cursor.execute(select_sql)
        for row in cursor.fetchall():
            print(row)

except sqlite3.Error as e:
    print(f"数据库操作出错：{e}")
finally:
    if 'cursor' in locals():
        try:
            cursor.close()
        except:
            pass